
/*
 * $Id$
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 *
 * Copyright (c) $Date$ Timothy Ginder. All Rights Reserved.
 *
 */

package controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * The back end for the database.
 * @author Timothy Ginder
 * @version 1.02
 */
public final class DataBase {

    /**Sets the timeout for query.*/
    private static final int TIMEOUT = 30;

    /**The SQL Connection.*/
    private static Connection localconnection;

    /**The SQL Statement.*/
    private static Statement localstatement;

    /**
     * Basic Database constructor.
     * @throws ClassNotFoundException if not found.
     * @throws SQLException if not available.
     */
    public DataBase() throws ClassNotFoundException, SQLException {
        // load the sqlite-JDBC driver using the current class loader
        Class.forName("org.sqlite.JDBC");

        // Connect to the local database files
        localconnection = null;
        //"jdbc:sqlite::memory:"

        try {
        // create a database connection
        localconnection = DriverManager.getConnection("jdbc:sqlite:sample.db");
        localstatement = DataBase.initalizeDatabase(localconnection);
        } catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found
            System.err.println(e.getMessage());
        }

    } //End DataBase constructor

    /**
     * Getter for this Database.
     * @return the database
     */
    public DataBase getDataBase() {
        return this;
    } //End of getDataBase method

    /**
     * Getter for statement.
     * @return the statement.
     */
    public static Statement getStatement() {
        return localstatement;
    } //End of getStatement method

    /**
     * Getter for connection.
     * @return the statement.
     */
    public Connection getConnection() {
        return localconnection;
    } //End of getConnection method

    /**
     * Initializes the whole Database.
     * @param connection the database connection.
     * @return the SQL statement.
     * @throws SQLException if a value is not available.
     */
  public static Statement initalizeDatabase(final Connection connection)
          throws SQLException {

      Statement statement = connection.createStatement();
      statement.setQueryTimeout(TIMEOUT);  // set timeout to 30 seconds
      statement.executeUpdate("drop table if exists user");
      statement.executeUpdate("drop table if exists conference");

      initializeUsers(statement);
      initializeConferences(statement);

      return statement;
  } //End initalizeDatabase method

  /**
   * Initializes the Users with some basic data.
   * @param statement the SQL statement.
   * @throws SQLException if a value is not available.
   */
  public static void initializeUsers(final Statement statement)
          throws SQLException {

    statement.executeUpdate("create table user (username string,"
            + " lastname string, firstname string, password string,"
            + "conference string, role string)");

    statement.executeUpdate("insert into user values"
            + "('blee', 'Lee', 'Brandon', '123456',"
            + " 'Paris', 'Author')");

    statement.executeUpdate("insert into user values"
            + "('tgin', 'Ginder', 'Timothy', '123456',"
            + " 'Paris', 'Author')");
  } //End intializeUsers method

  /**
   * Initializes the Conferences with some basic data.
   * @param statement the SQL statement.
   * @throws SQLException if a value is not available.
   */
  public static void initializeConferences(final Statement statement)
          throws SQLException {

      statement.executeUpdate("create table conference (title string,"
      + " confdate string, user string, "
      + "role string, papername string, paperbody string"
      + " paperauthor string, reviewer string, review string, status string,"
      + " manduedate string, revduedate string, recduedate string,"
      + " PCUserName string, PCPW string)");

      statement.executeUpdate("insert into conference values "
     + "('Paris', '041215', 'blee', 'author', 'Fundimentals of Stuff',"
     + " 'tgin', 'blee', ' ', ' ', '031214', '71214', '121214', 'tgin',"
     + " '123456')");
  } //End intializeConferences method

} //End class
